The primary role of database systems is to translate high-level, abstract declarations of data objects, and manipulations and searches for those objects. Ideally, those declarations and manipulations are translated efficiently by database systems into I/O requests sent to simple, linearly addressed, blocked, persistent storage devices. Often, database systems must perform such translations for multiple applications. Those applications may use multiple databases across different user and schema security levels, application types, database session types, and priorities and classes of I/O requests.
In many situations, the order in which I/O requests are sent to a storage system impacts the efficiency of the system. For example, when both a small, high-priority I/O request and a large, low-priority I/O requests need to be sent to a storage system, it would not be efficient for a database server to send the large, low-priority I/O request ahead of the small high-priority I/O request.
Various techniques have been developed to ensure that I/O requests are sent to storage systems in an intelligent manner. For example, U.S. patent application Ser. No. 11/716,364, entitled “Management Of Shared Storage I/O Resources”, which is incorporated herein by this reference, describes techniques for queuing I/O requests within a database server, and issuing those requests to a storage system in an intelligent manner. However, having database servers issue I/O requests in an intelligent manner does not ensure the optimal handling of the I/O requests by the storage system.
Specifically, storage systems typically handle I/O requests on a First-In-First-Out (FIFO) basis. To the extent that storage systems deviate from FIFO processing, the deviation involves reordering I/O requests to improve disk efficiency. Such reordering is performed without any regard for the purpose behind the I/O requests. Consequently, when multiple database servers are sending I/O requests for multiple databases to the same storage system (or the same storage device within a storage system), the storage system may end up processing lower priority I/O requests before processing higher priority I/O, even though each individual database server is independently issuing its I/O requests to the storage system in an optimal sequence.
Multiple types of workloads and databases often share storage. Unfortunately, running multiple types of workloads and databases on shared storage often leads to performance and response time problems between applications and mixed workloads. For example, large parallel queries on one production data warehouse can impact the performance of critical queries on another production data warehouse. Also, a data load on a data warehouse can impact the performance of critical queries also running on the same data warehouse. It is possible to mitigate these problems by over-provisioning the storage system, but this diminishes the cost-savings benefit of shared storage. It is also possible to schedule non-critical tasks at off-peak hours, but this manual process is laborious. It becomes impossible when databases have different administrators who do not coordinate their activities.
U.S. application Ser. No. 12/563,073, filed on Sep. 18, 2009, describes an approach for managing, within a storage system, the sequence in which I/O requests are processed by the storage system based, at least in part, on one or more logical characteristics of the I/O requests. The logical characteristics may include, for example, the identity of the user for whom the I/O request was submitted, the service that submitted the I/O request, the database targeted by the I/O request, an indication of a consumer group to which the I/O request maps, the reason why the I/O request was issued, a priority category of the I/O request, etc. Such logical characteristics differ fundamentally from the physical characteristics of the I/O requests, such as the storage location of the data targeted by the I/O request and the amount of data involved in the I/O operation specified by the I/O request.
In addition, it is also desirable in many situations be able to manage the amount of resources that can be consumed by the different sets of workloads in the system. This is because many resources, such as a disk drive, can only service one I/O request at a time. Therefore, when multiple workloads are accessing the data on a disk drive concurrently, the I/O requests that are not being serviced must wait in a disk queue. The wait incurred in this disk queue can result in very long latencies for an I/O request. From a user's perspective, if his workload suffers from long I/O latencies because of the interference from other workloads, it can make the sharing of the disk drive by multiple workloads problematic.
Therefore, there is a need for an improved approach to manage workloads so that resources are properly allocated among the existing workloads. The workload management should be implemented in a way that allows accurate assessment of resource usage by the workloads, and also provides for precise enforcement of any usage limits that may be configured for the workloads.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.